package com.zxj.geekstudy.spring.db;

import com.mysql.jdbc.StringUtils;
import com.zxj.geekstudy.spring.db.po.Student;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 2）使用事务，PrepareStatement 方式，批处理方式，改进上述操作。
 */
public class PrepareStatementDB {

    public static Connection connection;

    static {
        try {
            connection = initConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection initConnection() throws Exception {
        // 1.注册数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.与数据库建立连接
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/geek_study", "root", "root");
    }

    public int insert(String name, BigDecimal score) throws Exception {
        PreparedStatement ps = null;
        try {
            String sql = "INSERT INTO `student` (`name`, `score`) VALUES ( ?, ? )";
            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            ps.setBigDecimal(2, score);
            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
    }

    public int update(String name, BigDecimal score) throws Exception {
        if (StringUtils.isEmptyOrWhitespaceOnly(name) || score == null) {
            return 0;
        }
        PreparedStatement ps = null;
        try {
            String sql = "UPDATE `student` SET `score`= ? WHERE (`name` = ?)";
            ps = connection.prepareStatement(sql);
            ps.setBigDecimal(1, score);
            ps.setString(2, name);
            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
    }

    public int delete(String name) throws Exception {
        if (StringUtils.isEmptyOrWhitespaceOnly(name)) {
            return 0;
        }
        PreparedStatement ps = null;
        try {
            String sql = "DELETE FROM `student` WHERE (`name` = ?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1, name);
            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
    }

    public List<Student> query(String name) throws Exception {
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            String sql = "SELECT * FROM `student`";
            if (!StringUtils.isEmptyOrWhitespaceOnly(name)) {
                sql += " WHERE name = ?";
            }
            ps = connection.prepareStatement(sql);
            if (!StringUtils.isEmptyOrWhitespaceOnly(name)) {
                ps.setString(1, name);
            }
            resultSet = ps.executeQuery();
            List<Student> list = new ArrayList<>();
            // 5.处理结果集（针对查询）
            while (resultSet.next()) {
                // 一次循环处理一行，一行转换成一个对象
                Student student = new Student(resultSet.getInt(1), resultSet.getString(2), resultSet.getBigDecimal(3));
                list.add(student);// 添加到集合
            }
            return list;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
    }

    public static void main(String[] args) throws Exception {
        try {
            connection.setAutoCommit(false);
            PrepareStatementDB statementDB = new PrepareStatementDB();
            statementDB.insert("stu1", new BigDecimal(10));
            statementDB.insert("stu2", new BigDecimal(20));
            statementDB.insert("stu3", new BigDecimal(30));
            System.out.println(statementDB.query("stu1"));
            System.out.println(statementDB.query(null));
            statementDB.update("stu1", new BigDecimal(100));
            statementDB.delete("stu2");
            System.out.println(statementDB.query(null));
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            connection.rollback();
            // 关闭资源
            connection.close();
        }

    }
}
